package com.archgeek.calcite.test;

import com.alibaba.druid.sql.ast.SQLCommentHint;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLLimit;
import com.alibaba.druid.sql.ast.SQLOrderBy;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectGroupByClause;
import com.alibaba.druid.sql.ast.statement.SQLSelectItem;
import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.alibaba.druid.sql.ast.statement.SQLTableSource;
import com.alibaba.druid.sql.dialect.hive.parser.HiveStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.sql.parser.SQLParserFeature;
import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor;
import com.alibaba.druid.stat.TableStat;
import com.alibaba.druid.util.JdbcConstants;
import com.alibaba.druid.util.JdbcUtils;
import com.google.common.base.Joiner;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Created by Lancerlin
 * date: 2018/9/12 23:57
 */
public class SQLUtils {
    private static final org.slf4j.Logger logger = LoggerFactory.getLogger(SQLUtils.class);
    //static Logger LOGGER = Logger.getLogger(SQLUtils.class);

    private static final String sqlWindowPattern = "sql_window\\s*\\(.*\\)\\s*";

    /**
     * 获取groupby里面的window信息
     *
     * @param groupbyString
     * @return
     */
    public static String getWindowString(String groupbyString) {
        if (StringUtils.isBlank(groupbyString)) {
            return "";
        }

        Pattern r = Pattern.compile(sqlWindowPattern);
        Matcher m = r.matcher(groupbyString);
        if (m.find()) {
            logger.info("Found value: " + m.group(0));
            String windowStr = m.group(0).trim();
            return windowStr;
        }
        logger.info("getWindowString|NO sql_window");
        return "";
    }

    /**
     * 获取选择的列,拼凑
     * first(fid)&watermark(fdatetime, 60)&MAX(fage) AS fage_max&MAX(fscores) AS fscore_max
     *
     * @param info
     * @return
     */
    public static String getSelectString(SqlParseInfo info) {
        StringBuilder sb = new StringBuilder();
        List<ImmutablePair<String, String>> selectPairs = info.getSelectPairs();
        if (CollectionUtils.isNotEmpty(selectPairs)) {
            for (int i = 0; i < selectPairs.size(); i++) {
                ImmutablePair<String, String> pair = selectPairs.get(i);
                if (i == selectPairs.size() - 1) {
                    sb.append(pair.getLeft());
                    if (StringUtils.isNotBlank(pair.getRight())) {
                        sb.append(" AS ").append(pair.getRight());
                    }
                } else {
                    sb.append(pair.getLeft());
                    if (StringUtils.isNotBlank(pair.getRight())) {
                        sb.append(" AS ").append(pair.getRight());
                    }
                    sb.append("&");
                }
            }
        }
        return sb.toString();
    }

    /**
     * 获取windo的信息,四个参数
     *
     * @param windowInfo
     * @return sql_window(ftime, 60, 60, 0, 60, 60, append)
     */
    public static String[] getWindowCol(String windowInfo) {
        if (StringUtils.isBlank(windowInfo)) {
            return null;
        }

        String trim = windowInfo.trim();
        if (trim.endsWith(",")) {
            trim = trim.substring(0, trim.length() - 1);
        }
        if (trim.startsWith("sql_window(")) {
            trim = trim.substring(11, trim.length() - 1);
            logger.info("trim :" + trim);
            String[] ss = StringUtils.splitPreserveAllTokens(trim, ",");
            if (ss.length == 7) {
                return ss;
            }
            throw new RuntimeException("不符合规则，请验证 str: " + windowInfo + " split result : " + ss);
        } else {
            logger.info("sql_window string error|"+windowInfo);
        }
        return null;

    }


    public static List<String> getColumnsByTable(String tbName, String sql) {
        String dbType = JdbcConstants.MYSQL.name();
        boolean abortColumn = false;
        Set<String> abortColumns = null;
        // TODO
        // *******这里隐藏规则*******，
        // 当表名为UNKNOWN的时候，不获取fvserion跟 fmodify_time
        if ("UNKNOWN".equalsIgnoreCase(tbName)) {
            abortColumn = true;
            abortColumns = new HashSet<>();
            abortColumns.add("fversion");
            abortColumns.add("fmodify_time");
        }

        //格式化输出
        String result = com.alibaba.druid.sql.SQLUtils.format(sql, dbType);
        logger.info(result); // 缺省大写格式
        List<SQLStatement> stmtList = com.alibaba.druid.sql.SQLUtils.parseStatements(sql, dbType);

        //解析出的独立语句的个数
        List<String> list = null;
        logger.info("size is:" + stmtList.size());
        for (int i = 0; i < stmtList.size(); i++) {

            SQLStatement stmt = stmtList.get(i);
            MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
            stmt.accept(visitor);

            //获取表名称
            logger.info("Tables : " + visitor.getTables());
            //获取操作方法名称,依赖于表名称
            logger.info("Manipulation : " + visitor.getTables());
            //获取字段名称
            logger.info("fields : " + visitor.getColumns());

            Collection<TableStat.Column> columns = visitor.getColumns();
            list = new ArrayList<>(columns.size());
            for (TableStat.Column column : columns) {
                if (tbName.equalsIgnoreCase(column.getTable())) {
                    String cname = column.getName().toLowerCase();
                    if (abortColumn) {
                        if (!abortColumns.contains(cname)) {
                            list.add(cname);
                        }
                    } else {
                        list.add(cname);
                    }
                }
            }

        }
        return list;
    }


    public static SqlParseInfo getInfoByParseSQL(String sql) {

        SqlParseInfo sqlParseInfo = new SqlParseInfo();

//        String sql = " select eventId as id, eventKey,eventName,flag from event where eventId = ? and eventKey = ? and eventName = ?";
        //使用mysql解析
        HiveStatementParser sqlStatementParser = new HiveStatementParser(sql, new SQLParserFeature[]{SQLParserFeature.KeepComments});
        //解析select查询
        SQLSelectStatement sqlStatement = (SQLSelectStatement) sqlStatementParser.parseSelect();
        SQLSelect sqlSelect = sqlStatement.getSelect();
        //获取sql查询块
        SQLSelectQueryBlock sqlSelectQuery = (SQLSelectQueryBlock) sqlSelect.getQuery();

        StringBuffer out = new StringBuffer();
        //创建sql解析的标准化输出
        SQLASTOutputVisitor sqlastOutputVisitor = com.alibaba.druid.sql.SQLUtils.createFormatOutputVisitor(out, null, JdbcUtils.HIVE);

        List<ImmutablePair<String, String>> selectPairs = new ArrayList();
        //解析select项
        ImmutablePair<String, String> pair = null;
        out.delete(0, out.length());
        for (SQLSelectItem sqlSelectItem : sqlSelectQuery.getSelectList()) {
            String[] expr = sqlSelectItem.toString().split(" AS ");
            if(expr.length  <=  0 ||expr.length > 2){
                logger.error("expr.length  <=  0 ||expr.length > 2|"+sqlSelectItem.toString());
                throw new RuntimeException("expr.length  <=  0 ||expr.length > 2|"+sqlSelectItem.toString());
            }

            String exprStr = expr[0];
            pair = new ImmutablePair<>(exprStr, sqlSelectItem.getAlias() == null ? "" : sqlSelectItem.getAlias());
            logger.warn("SELECT|item|"+sqlSelectItem.toString());
            selectPairs.add(pair);
            if (out.length() > 1) {
                out.append(",");
            }
            sqlSelectItem.accept(sqlastOutputVisitor);
//            logger.info("alis " + sqlSelectItem.getAlias());
        }
        sqlParseInfo.setSelectPairs(selectPairs);
        logger.warn("SELECTPairs|"+selectPairs);
        logger.warn("SELECT|" + out);

        //解析from
        out.delete(0, out.length());
        SQLTableSource from = sqlSelectQuery.getFrom();
        if (from != null) {
            from.accept(sqlastOutputVisitor);
            logger.info("FROM|" + out);
            sqlParseInfo.setRelationByString(out.toString());
            sqlParseInfo.setTableSource(from);
        }

        //解析where
        out.delete(0, out.length());
        SQLExpr where = sqlSelectQuery.getWhere();
        if (where != null) {
            where.accept(sqlastOutputVisitor);
            logger.info("WHERE|" + out);
            sqlParseInfo.setWhereString(out.toString());
        }

        //解析hint
        out.delete(0, out.length());
        List<SQLCommentHint> hints = sqlSelectQuery.getHints();
        if (hints != null && hints.size() > 0) {
            for(SQLCommentHint hint :hints){
                hint.accept(sqlastOutputVisitor);
                logger.info("HINT|" + out.toString());
                out.delete(0, out.length());
            }
            sqlParseInfo.setHints(hints.toString());
        }


        out.delete(0, out.length());
        SQLSelectGroupByClause groupBy = sqlSelectQuery.getGroupBy();
        if (groupBy != null) {
            groupBy.accept(sqlastOutputVisitor);
            logger.info("GROUP BY|" + out);

            sqlParseInfo.setGroupByString(out.toString().replace("GROUP BY", ""));
        }

        out.delete(0, out.length());
        SQLOrderBy orderBy = sqlSelectQuery.getOrderBy();
        if (orderBy != null) {
            orderBy.accept(sqlastOutputVisitor);
            logger.info("ORDER BY|" + out);
            sqlParseInfo.setOrderbyString(out.toString().replace("ORDER BY", ""));

        }
      out.delete(0, out.length());
      SQLLimit limit = sqlSelectQuery.getLimit();
      if (null != limit) {
        limit.accept(sqlastOutputVisitor);
        logger.info("LIMIT | " + out);
        sqlParseInfo.setLimitString(out.toString());
      }

      return sqlParseInfo;
    }
//
//  public static void main(String[] args) {
//
//    String sql10 = "select t1.*,t2.* from\n" +
//      "(\n" +
//      "  select distinct factivity_channel,fuid\n" +
//      "  from lx_dwd.dwd_maiya_invite_activity_user_channel_detail \n" +
//      "  where cast(fbind_time as date)>='2022-04-14'\n" +
//      "  and fuid in \n" +
//      " (\n" +
//      "   select distinct fuid  from dp_xxhf_mart.t_maiya_order_detail\n" +
//      "   where  date(fcreate_time) between '2022-04-23' and '2022-04-23'\n" +
//      "   and forder_state>=360\n" +
//      "   and (fuid < 3000000 or fuid > 5000000) \n" +
//      "   and fmaiya_order_plat_source=2\n" +
//      " )\n" +
//      ") t1\n" +
//      "left join dp_fksx_mart.north_maiya_rcorder_id_total_out t2\n" +
//      "on t2.fuid=t1.fuid\n" +
//      "--limit 10";
//
//
//    System.out.println(getInfoByParseSQL(sql10));
////    System.out.println(com.alibaba.druid.sql.SQLUtils.parseSingleStatement(sql10, JdbcConstants.HIVE, new SQLParserFeature[]{SQLParserFeature.KeepComments}));
//  }



  public static Map<String,Set<String>> getConditionByParseSQL(String selectSql) {
        long startTs = System.currentTimeMillis();
        String dbType = JdbcConstants.MYSQL.name();
        List<SQLStatement> stmtList = com.alibaba.druid.sql.SQLUtils.parseStatements(selectSql, dbType);
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        stmtList.get(0).accept(visitor);
        List<TableStat.Condition> conditionList = visitor.getConditions();
        Map<String,Set<String>> conditionInfo = new HashMap<String,Set<String>>();
        for(TableStat.Condition condition:conditionList){
            String table = condition.getColumn().getTable();
            String column = condition.getColumn().getName();
            if(!conditionInfo.containsKey(table)){
                conditionInfo.put(table,new HashSet<String>());
            }
            Set<String> columnList = conditionInfo.get(table);
            columnList.add(column);
        }
        Map<TableStat.Name, TableStat> tableStat = visitor.getTables();
        for(TableStat.Name table : tableStat.keySet()){
            if(!conditionInfo.containsKey(table.getName())){
                conditionInfo.put(table.getName(),new HashSet<String>());
            }
        }
        long costMs = System.currentTimeMillis() - startTs;
        logger.info("parse sql cost: {} ms", String.valueOf(costMs));
        if(costMs > 500){
            logger.warn("parse sql:{},  cost: {} ms", selectSql, String.valueOf(costMs));
        }
        return conditionInfo;
    }


    public  static String vertifysql(Map<String,Set<String>> conditionInfo){
        Map<String,Set<String>> warntableMap = new HashMap<String,Set<String>>();
        for(String table : conditionInfo.keySet()){
            Set<String> conditionList = conditionInfo.get(table);
            Set<String> partitionList = null;//MetaInfoUtils.getInstance().getPartitionInfoByTable(table);
            if(CollectionUtils.isNotEmpty(partitionList)){
                if(CollectionUtils.isEmpty(conditionList)){
                    warntableMap.put(table,partitionList);
                }
                for(String condition:conditionList){
                    if(partitionList.contains(condition)){
                        warntableMap.put(table,partitionList);
                        break;
                    }
                }
            }
        }
        if(!warntableMap.isEmpty()){
            StringBuffer warnstr  = new  StringBuffer("为下列库表添加分区条件查询更快哦！");
            for(String warntable : warntableMap.keySet()){
                warnstr.append("库表："+warntable+",对应分区字段："+ Joiner.on(",").join(warntableMap.get(warntable)));
            }
            return warnstr.toString();
        }

        return null;

    }
}
